package com.gxuwz.lhleavesys.dao.impl;

import com.gxuwz.lhleavesys.dao.TeacherDao;
import com.gxuwz.lhleavesys.db.DbUtil;
import com.gxuwz.lhleavesys.entity.Teacher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class TeacherDaoImpl implements TeacherDao {
    @Autowired
    private DbUtil dbUtil;

    /**
     * 查询 BY ID
     * 
     * @param instID
     * @return
     */
    @Override
    public Teacher findByInstID(String instID) {
        Teacher teacher = null;
        String sql = "SELECT * FROM sys_instructor WHERE instID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, instID);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    teacher = new Teacher();
                    teacher.setInstID(rs.getString("instID"));
                    teacher.setName(rs.getString("name"));
                    teacher.setCollegeID(rs.getString("collegeID"));
                    teacher.setTelNumber(rs.getString("telNumber"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return teacher;
    }

    /**
     * 模糊查询所有
     * 
     * @return
     * @throws Exception
     */
    @Override
    public List<Teacher> findAllorOne(String instID, String name) {
        List<Teacher> instructors = new ArrayList<>();
        String sql;
        if ((instID != null && !instID.isEmpty()) && (name != null && !name.isEmpty())) {
            sql = "SELECT * FROM sys_instructor WHERE instID LIKE ? or name LIKE ?";
        } else {
            sql = "SELECT * FROM sys_instructor WHERE instID LIKE ? and name LIKE ?";
        }
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            if (instID != null && !instID.isEmpty()) {
                ps.setString(1, "%" + instID + "%");
            } else {
                ps.setString(1, "%"); // 占位符需要设置一个值，这里设置一个通配符表示匹配所有
            }
            if (name != null && !name.isEmpty()) {
                ps.setString(2, "%" + name + "%");
            } else {
                ps.setString(2, "%"); // 同样需要设置一个值
            }
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Teacher teacher = new Teacher();
                    teacher.setInstID(rs.getString("instID"));
                    teacher.setName(rs.getString("name"));
                    teacher.setCollegeID(rs.getString("collegeID"));
                    teacher.setTelNumber(rs.getString("telNumber"));
                    teacher.setPassword(rs.getString("password"));
                    instructors.add(teacher);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return instructors;
    }

    /**
     * 新增
     * 
     * @param teacher
     * @return
     */
    @Override
    public boolean insert(Teacher teacher) {
        String sql = "INSERT INTO sys_instructor (instID, name, collegeID, telNumber, password) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, teacher.getInstID());
            ps.setString(2, teacher.getName());
            ps.setString(3, teacher.getCollegeID());
            ps.setString(4, teacher.getTelNumber());
            ps.setString(5, teacher.getPassword());
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 修改
     * 
     * @param teacher
     * @return
     */
    @Override
    public boolean update(Teacher teacher, String id) {
        String sql = "UPDATE sys_instructor SET name = ?,  collegeID= ?, instID = ?, telNumber = ?, password = ? WHERE instID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, teacher.getName());
            ps.setString(2, teacher.getCollegeID());
            ps.setString(3, teacher.getInstID());
            ps.setString(4, teacher.getTelNumber());
            ps.setString(5, teacher.getPassword());
            ps.setString(6, id);
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除 BY ID
     * 
     * @param instID
     * @return
     */
    @Override
    public boolean delete(String instID) {
        String sql = "DELETE FROM sys_instructor WHERE instID = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, instID);
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public Teacher findInstructorByinstName(String name) {
        Teacher teacher = null;
        String sql = "SELECT * FROM sys_instructor WHERE name = ?";
        try (Connection conn = dbUtil.getConn();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    teacher = new Teacher();
                    teacher.setInstID(rs.getString("instID"));
                    teacher.setName(rs.getString("name"));
                    teacher.setCollegeID(rs.getString("collegeID"));
                    teacher.setTelNumber(rs.getString("telNumber"));
                    teacher.setPassword(rs.getString("password"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return teacher;
    }

    public List<Teacher> query(Teacher teacher) throws SQLException {
        List<Teacher> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        List<Object> paramsList = new ArrayList<Object>();
        String sql = "select * from sys_instructor where 1=1";
        try {
            // 添加查询处理过程
            conn = dbUtil.getConn();
            // 设置占位符？对应的参数
            if (teacher != null && teacher.getName() != null) {
                sql += " and  name=?";
                paramsList.add(teacher.getName());
            }
            if (teacher != null && teacher.getPassword() != null) {
                sql += " and  password=?";
                paramsList.add(teacher.getPassword());
            }
            pStmt = conn.prepareStatement(sql);
            int paramIndex = 1;
            for (Object obj : paramsList) {
                if (obj instanceof String) {
                    pStmt.setString(paramIndex++, (String) obj);
                } else if (obj instanceof Integer) {
                    pStmt.setInt(paramIndex++, (Integer) obj);
                }
            }
            // 发送SQL、执行并返回结果集(ResultSet)
            rs = pStmt.executeQuery();
            // 遍历结果集ResultSet
            while (rs.next()) {
                Teacher t = new Teacher();
                // 从rs中取出每一列对应的值
                t.setInstID(rs.getString("instID"));
                t.setName(rs.getString("name"));
                t.setCollegeID(rs.getString("collegeID"));
                t.setTelNumber(rs.getString("telNumber"));
                t.setPassword(rs.getString("password"));
                // 加入返回的list
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("执行查询数据操作发生异常:" + e.getMessage(), e);
        } finally {
            // 关闭资源
            dbUtil.close(rs, pStmt, conn);
        }
    }

}
